const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const constant = require('../data/constant');
const auth = require('../middleware/auth');
const tools = require('../data/tools');

// 资源基本信息接口
router.post('/basic',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM files WHERE file_id = ?;"+
            "SELECT username FROM user where uid = "+
            "(SELECT distinct uploader FROM files where file_id = ?);"
            let  sqlParams = [
                req.body.file_id,
                req.body.file_id
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data[0].length >= 1){
                        // 处理数据
                        let basic = {
                            'title':res_data[0][0].name,
                            'originalname':res_data[0][0].originalname,
                            'type':res_data[0][0].type.toUpperCase(),
                            'download_url':res_data[0][0].path,
                            'tags':res_data[0][0].tags.split(' '),
                            'course':res_data[0][0].course,
                            'desc':res_data[0][0].desc,
                            'uploader':res_data[1][0].username,
                            'time':res_data[0][0].time.slice(0,10),
                            'version':res_data[0][0].version,
                            'download_count':res_data[0][0].download_count,
                            'score':res_data[0][0].score,
                            'recommend_tag':res_data[0][0].recommend_tag
                        };
                        // console.log(basic);
                        res.send({
                            res:{
                                ok:1,
                                data:basic
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"资源不见了，可能是该资源不存在或被删除，请联系管理员！"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});

// 资源版本信息接口
router.post('/version',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true, // 支持执行多条 sql 语句
        'dateStrings':true
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM history_version history WHERE file_id = ? order by time desc;"+
            "SELECT username FROM user where uid = "+
            "(SELECT distinct uploader FROM history_version where file_id = ?);"
            let  sqlParams = [
                req.body.file_id,
                req.body.file_id
            ];
            //console.log(req.body,sql);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    //console.log(res_data);
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = []
                        for(let i in res_data[0]){
                            let temp = {
                                version_id:res_data[0][i].version,
                                update_reason:res_data[0][i].update_log,
                                uploader:res_data[1][0].username,
                                // time:tools.convert_time(res_data[0][i].time)
                                time:res_data[0][i].time
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"无历史版本信息"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});

// 获取评论接口
router.post('/comments',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM comment where file_id = ?;"
            let  sqlParams = [
                req.body.file_id,
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = []
                        for(let i in res_data){
                            let temp = {
                                uid:res_data[i].uid,
                                username:res_data[i].username,
                                content:res_data[i].content,
                                time:tools.convert_time(res_data[i].time)
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"没有评论！"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});

// 提交推荐信息
router.post('/commit_recommend',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "insert into recommend(uid,file_id,reason,username) values(?,?,?,?);"+
            "update files set recommend_tag = true where file_id = ?;"
            let  sqlParams = [
                req.body.uid,
                req.body.file_id,
                req.body.reason,
                req.body.username,
                req.body.file_id
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    if(err.errno == 1062){
                        res.statusCode = 200;
                        res.send({
                            res:{
                                ok:0,
                                msg:"您已经提交过对该资源的推荐信息，请勿重复提交！"
                            }
                        })
                    }else{
                        res.statusCode = 500;
                        res.send({
                            'res':{
                                'ok': 0,
                                'msg':'数据库连接失败'
                            }
                        });
                    }
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data[0].affectedRows == 1 && res_data[1].affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                data:"提交成功"
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"提交失败！"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});

// 获取教师推荐信息
router.post('/get_recommend',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "select * from recommend where file_id = ?;"
            let  sqlParams = [
                req.body.file_id
            ];
            //console.log(req.body,sql);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = []
                        for(let i in res_data){
                            let temp = {
                                uid:res_data[i].uid,
                                teacher:res_data[i].username,
                                reason:res_data[i].reason,
                                time:tools.convert_time(res_data[i].time),
                                like:res_data[i].like,
                                dislike:res_data[i].dislike,
                                likeStatus:'like',
                                dislikeStatus:'dislike'
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"无教师推荐信息"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});

// 提交点赞
router.post('/commit_like',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "update recommend set `like` = ?,`dislike` = ? where `file_id` = ? and `uid` = ?"
            let  sqlParams = [
                req.body.like,
                req.body.dislike,
                req.body.file_id,
                req.body.uid
            ];
            //console.log(req.body,sql);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                data:"操作成功！"
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"提交点赞信息失败！"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});
module.exports = router;